﻿using Common;
using MySql.Data.MySqlClient;
using NB.MGJ.FrameWork;
using NB.MGJ.Model;
using NB.MGJ.Model.AntiFake;
using System;
using System.Configuration;
using System.Data;
using System.Text;

namespace NB.MGJ.Service.AntiFake
{
    public class AntiFakeVerifyService
    {
        public BaseRepository _base = new BaseRepository();

        //public DataTable GetCodeService(string _enterperiseCode, string _batchCode, string _sqlWhere)
        //{
        //    if (string.IsNullOrWhiteSpace(_enterperiseCode) || string.IsNullOrWhiteSpace(_batchCode) || string.IsNullOrWhiteSpace(_sqlWhere))
        //    {
        //        throw new ArgumentNullException("必要参数不可为空");
        //    }

        //    return null;
        //}

        public DataTable GetCodeService(string ecode, string fCode, string Code, string bcode, string sqlWhere)
        {      //PC端查询 不更新查询次数并且不添加查询记录
            //手机端或者其他终端查询的时候  需要更新查询次数和最新的查询时间 且 需要添加查询记录
            try
            {
                //string strSql = "";
                //strSql += "SET @EntepriseId = (SELECT  A.ID  FROM  organization_enterprise A WHERE  A.`Code` = "+ecode+");";
                //strSql += "SET @Gen_BatchId = (SELECT C.ID FROM  codemanage_gencodebatch C  WHERE C.EnterpriseId = @EntepriseId AND C.Batch = " + code + ");";
                //strSql += "SET @ProductId = (SELECT E.ProductId FROM nbcodemain.antifake_digitcode" + fCode + " E WHERE E.Fw_Code = " + bCode + " AND E.STATS=0);";
                //strSql += "SET @stats = (SELECT	E.stats	FROM nbcodemain.antifake_digitcode" + fCode + " E	WHERE	E.Fw_Code =" + bCode + ");";
                //strSql += "SELECT B.LabelImage,B.Query_Message_Content,B.Query_Result_Content,@ProductId ProductId,@stats stats,(SELECT C.ProductName FROM product_product C WHERE  C.ID = @ProductId) ProductName,B.EnterpriseID,D.EnterpriseName,B.EnterpriseUse   FROM codemanage_gencode B LEFT JOIN organization_enterprise D ON D.ID = B.EnterpriseId WHERE  B.EnterpriseId = @EntepriseId AND   B.PatchId = @Gen_BatchId";

                string strSql = string.Format(@"SELECT A.*,
                             B.EnterpriseName,
                           D.Query_Result_Content,
                            E.ProductName,
                          (SELECT LabelImage  FROM  codemanage_gencode G WHERE  G.EnterpriseId={0} AND  patchid=(select id from codemanage_gencodebatch where Batch={1} and EnterpriseId={0})) AS LabelImage,
                         (SELECT EnterpriseUse  FROM  codemanage_gencode G WHERE  G.EnterpriseId={0} AND patchid=(select id from codemanage_gencodebatch where Batch={1} and EnterpriseId={0})) AS EnterpriseUse

                            FROM
                           nbcodemain.antifake_digitcode{3} A LEFT JOIN organization_enterprise B ON B.ID = A.EnterpriseId LEFT JOIN codemanage_gencodebatch C ON C.EnterpriseId = A.EnterpriseId  AND A.Stats=0 AND C.Batch ={1} AND C.ViewFlag  = 1 LEFT JOIN codemanage_gencode D ON D.EnterpriseId = A.EnterpriseId AND D.PatchId = C.ID LEFT JOIN product_product E ON E.ID = A.ProductId WHERE A.Stats = 0 {2}", ecode, bcode, sqlWhere, fCode);

                    return _base.GetDataTable(strSql);
            }
            catch
            {
                return null;
            }
        }

        public DataTable GetCodeModel(string code, string type)
        {
            try
            {
            var ECode = string.Empty;
            var dbName = "nbcodemain";
            var tableParm = "antifake_digitcode";
            var Fw_Code = string.Empty;
            if (type == "Fw_Code" || type == "Wl_Code")
            {
                ECode = code.Substring(0, 8);
            }
            else if (type == "Fw_CodeSecret")
            {
                    Fw_Code = MiMa.Jiemi(code, AppConst.DigitCodeKey);
                    ECode = Fw_Code.Substring(0, AppConst.SubStrLen);
            }
                var source = dbName + "." + tableParm + ECode;

            StringBuilder sb = new StringBuilder();
                sb.Append("select * from " + source + " where " + type + " ='" + code + "'");
            var sql = sb.ToString();

            var  result = _base.GetDataTable(sql);
            return result;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public DataTable GetCodeLogDis(string code, string ecode)
        {
            try
            {
                string strCode = MiMa.Jiemi(code.Trim(), AppConst.DigitCodeKey);

                string strSql = string.Format(@"select  Fw_Code,ProductId,QueryTime,QueryType,QueryProvince,QueryCity,QueryValue,QueryIp,
                valid  from nblogmain.`codelog" + ecode + "` where fw_code='{0}' or fw_Scode='{1}'", strCode, strCode);
                return _base.GetDataTable(strSql);
            }
            catch (Exception)
            {
                return null;
            }
        }

        public DataTable GetwebCodeLog(string GetEnterId, string code)
        {
            try
            {
                string strSql = string.Format(@"select  Fw_Code,ProductId,QueryTime,QueryType,QueryProvince,QueryIp,QueryCity,QueryValue,
                valid  from nblogmain.`codelog" + GetEnterId + "` where fw_scode='{0}' or fw_code='{1}'", code, code);
                var result = _base.GetDataTable(strSql);
                return result;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public string GetVerifyService(string code)
        {
            string sCode = string.Empty;
            code = MiMa.Jiemi(code.Trim(), AppConst.DigitCodeKey);
            sCode = code.Substring(0, AppConst.SubStrLen);
            string bcode = sCode.Substring(4, AppConst.SubBatchLen);
            string strSql = @"SELECT
                             A.Fw_Code,
                             A.CreateDate,
                             B.EnterpriseName,
                             D.Query_Result_Content,
                             E.ProductName,
                             LabelImage
                             FROM
                             nbcodemain.antifake_digitcode" + sCode + " A LEFT JOIN organization_enterprise B ON B.ID = A.EnterpriseId LEFT JOIN codemanage_gencodebatch C ON C.EnterpriseId = A.EnterpriseId AND  C.ProductId = A.ProductId AND C.ProductBatchId = A.ProductBatchId AND C.Batch =" + bcode + " AND C.ViewFlag  = 1 LEFT JOIN codemanage_gencode D ON D.EnterpriseId = A.EnterpriseId AND D.PatchId = C.ID LEFT JOIN product_product E ON E.ID = A.ProductId WHERE A.Fw_Code = '" + code + "' and D.ViewDel=0";
            return _base.ExecuteText<string>(strSql);
        }

        public string ReplyInfoService(int  EnterpriseId)
        {
            string strSql = "SELECT WebContent FROM antifake_queryreply where EnterpriseId=" + EnterpriseId;
            return _base.ExecuteText<string>(strSql);
        }

        public int GetCode(string code)
        {
            try
            {
                var _connectionString = ConfigurationManager.ConnectionStrings["mgj_basemysql"].ConnectionString;
                if (!string.IsNullOrWhiteSpace(_connectionString))
                {
                    using (MySqlConnection conn = new MySqlConnection(_connectionString))
                    {
                        using (MySqlCommand cmd = conn.CreateCommand())
                        {
                            conn.Open();
                            string fwcoded = string.Empty;
                            fwcoded = MiMa.Jiemi(code.Trim(), AppConst.DigitCodeKey);
                            fwcoded = fwcoded.Substring(0, AppConst.SubStrLen);
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = string.Format("SHOW TABLES FROM nbcodemain LIKE 'antifake_digitcode{0}'", fwcoded);
                            object _show = cmd.ExecuteScalar();
                            if (_show != null)
                            {
                                cmd.CommandText = string.Format(" select count(1) from nbcodemain.antifake_digitcode" + fwcoded + " where Fw_CodeSecret='" + code + "'");
                                return Convert.ToInt32(cmd.ExecuteScalar());
                            }
                        }
                    }
                }
            }
            catch { }
            return 0;
        }

        public int GetCodes(string code)
        {
            try
            {
                var _connectionString = ConfigurationManager.ConnectionStrings["mgj_basemysql"].ConnectionString;
                if (!string.IsNullOrWhiteSpace(_connectionString))
                {
                    using (MySqlConnection conn = new MySqlConnection(_connectionString))
                    {
                        using (MySqlCommand cmd = conn.CreateCommand())
                        {
                            conn.Open();
                            string fwcoded = string.Empty;
                            fwcoded = MiMa.Jiemi(code.Trim(), AppConst.DigitCodeKey);
                            fwcoded = fwcoded.Substring(0, AppConst.SubStrLen);
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = string.Format("SHOW TABLES FROM nbcodemain LIKE 'antifake_digitcode{0}'", fwcoded);
                            object _show = cmd.ExecuteScalar();
                            if (_show != null)
                            {
                                cmd.CommandText = string.Format(" select count(1) from nbcodemain.antifake_digitcode" + fwcoded + " where Fw_CodeSecret='" + code + "'");
                                return Convert.ToInt32(cmd.ExecuteScalar());
                            }
                        }
                    }
                }
            }
            catch { }
            return 0;
        }

        //查询信息插入记录表
        public int AddCodeSearchLog(CodeLog model, string mcode, string Code)
        {
            string sql = string.Format("select count(*) as num from nblogmain.`codelog{0}` where Fw_SCode='{1}' or Fw_Code = '{2}' order by QueryTime desc", mcode, Code, Code);
            var res = _base.GetSingle(sql);
            int resu = Convert.ToInt32(res);
            if (resu > 4)
            {
                model.Valid = 0;
            }
            string sqlstr = string.Format("INSERT INTO nblogmain.`codelog" + mcode + "`(Fw_SCode,Fw_Code,`QueryType`,`QueryIp`,`QueryTime`,`Valid`,`ProductId`,`EnterpriseId`,QueryProvince,QueryCity,SearchType)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',0)", model.Fw_Code, model.Fw_SCode, model.QueryType, model.QueryIp, model.QueryTime, model.Valid, model.ProductId, mcode, model.QueryProvince, model.QueryCity);
            var rulset = _base.ExecuteSql<int>(sqlstr);
            return rulset;
        }

        public int states(string Code)
        {
            var Tabname = MiMa.Jiemi(Code.Trim(), AppConst.DigitCodeKey).Substring(0, AppConst.SubStrLen);

              var sql = "select Stats from nbcodemain.antifake_digitcode" + Tabname + " WHERE Fw_CodeSecret='" + Code + "'";
            var res = _base.ExecuteText<string>(sql);
            var result = Convert.ToInt32(res);
            return result;
        }

        public string GetEnterId(string Tabcode, string Code)
        {
            try
            {
                string strSql = string.Format("SELECT EnterpriseId from nbcodemain.antifake_digitcode{0}  where Fw_CodeSecret='{1}'and stats!=1", Tabcode, Code);
                return _base.ExecuteText<string>(strSql);
            }
            catch
            {
                return null;
            }
        }

        public string allotcode(string code)
        {
            try
            {
                var fcode = code.Substring(0, 8);

                string strSql = "SELECT EnterpriseId FROM nbcodemain.antifake_digitcode" + fcode + " where Fw_Code=" + code + "";
                return _base.ExecuteText<string>(strSql);
            }
            catch
            {
                return null;
            }
        }

        public string state(string code)
        {
            try
            {
                var Scode = MiMa.Jiemi(code, AppConst.DigitCodeKey);
                var fcode = Scode.Substring(0, AppConst.SubStrLen);

                string strSql = string.Format("SELECT id from  nbcodemain.antifake_digitcode{0} where  Stats=0", fcode);
                return _base.ExecuteText<string>(strSql);
            }
            catch
            {
                return null;
            }
        }

        public int AddCodeLog(CodeLog model, string mcode)
        {
            string sqlstr = string.Format("INSERT INTO nblogmain.`codelog" + mcode + "`(Fw_SCode,Fw_Code,`QueryType`,`QueryIp`,`QueryTime`,`Valid`,`Pr oductId`,`EnterpriseId`,QueryProvince,QueryCity)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')", model.Fw_Code, model.Fw_SCode, model.QueryType, model.QueryIp, model.QueryTime, model.Valid, model.ProductId, model.EnterpriseId, model.QueryProvince, model.QueryCity);
            var rulset = _base.ExecuteSql<int>(sqlstr);
            return rulset;
        }

        public object searchtimes(string Code, string mcode)
        {
            string sql = string.Format("select count(*) as num from nblogmain.`codelog{0}` where Fw_SCode='{1}' or Fw_Code = '{2}' order by QueryTime desc", mcode, Code, Code);
            var res = _base.GetSingle(sql);
            return res;
        }

        public string FirstSearch(string Code, string mcode)
        {
            string sql = string.Format("SELECT  QueryTime FROM nblogmain.`codelog{0}` WHERE	Fw_SCode = '{1}' or Fw_Code = '{2}' ORDER BY QueryTime  LIMIT 1", mcode, Code, Code);
            var ress = _base.GetSingle(sql);
            if (ress == null)
            {
                return DateTime.Now.ToString();
            }
            string res = ress.ToString();

            return res;
        }

        public object querytype(string Code, string mcode)
        {
            string sql = string.Format("SELECT  QueryType FROM nblogmain.`codelog{0}` WHERE	Fw_SCode = '{1}'or Fw_Code = '{2}'  ORDER BY QueryTime LIMIT 1", mcode, Code, Code);
            var res = _base.GetSingle(sql);
            return res;
        }

      public string GetsuperEid(string eid)
        {
            string strSql = "SELECT SuperiorsOrganization FROM organization_enterprise where code=" + eid;
            return _base.ExecuteText<string>(strSql);
        }
    }
}